package link.tengfei.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import link.tengfei.bean.GengralUser;
import link.tengfei.util.ConnectionFactory;

public class UserListDaoImpl implements UserListDao {

	@Override
	public int findNum() {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		int total = 0;
		try {
			String sql = "select count(id) as total from gengral_user";
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return total;
	}

	@Override
	public List<GengralUser> findByPage(int page) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from gengral_user limit ?,10";
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, page);
			rs = psmt.executeQuery();
			List<GengralUser> users = new ArrayList<>();
			while (rs.next()) {
				GengralUser user = new GengralUser();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setSex(rs.getInt("sex"));
				user.setAge(rs.getString("age"));
				user.setNumber(rs.getString("number"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				user.setState(rs.getInt("state"));
				users.add(user);
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return null;
	}

	@Override
	public List<GengralUser> findByName(int page, String value, String nameOrnum) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from gengral_user where " + nameOrnum + "=? limit ?,10";
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, value);
			psmt.setInt(2, page);
			rs = psmt.executeQuery();
			List<GengralUser> users = new ArrayList<>();
			while (rs.next()) {
				GengralUser user = new GengralUser();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setSex(rs.getInt("sex"));
				user.setAge(rs.getString("age"));
				user.setNumber(rs.getString("number"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				user.setState(rs.getInt("state"));
				users.add(user);
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return null;
	}

	@Override
	public int findNumByName(String value, String nameOrnum) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		int total = 0;
		String sql = "select count(id) as total from gengral_user where " + nameOrnum + "=?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, value);
			rs = psmt.executeQuery();
			while (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return total;
	}

	@Override
	public int resetPwdById(int id) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		int total = 0;
		String sql = "update gengral_user set password=? where id=?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, "123456");
			psmt.setInt(2, id);
			total = psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, null);
		}
		return total;
	}

	@Override
	public int delete(int id, int state, String admin_username) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		int total = 0;
		String sql = "update gengral_user set state=?, delete_date=?,change_user=(select id from admin_user where username=?) where id=?";
		try {
			psmt = conn.prepareStatement(sql);
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");// 设置日期格式
			String deletedate = df.format(new Date());
			psmt.setInt(1, state);
			psmt.setString(2, deletedate);
			psmt.setString(3, admin_username);
			psmt.setInt(4, id);
			total = psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, null);
		}
		return total;
	}

	@Override
	public int getStateById(int id) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		int state = 0;
		try {
			String sql = "select state from gengral_user where id=?";
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			rs = psmt.executeQuery();
			while (rs.next()) {
				state = rs.getInt("state");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return state;
	}

	@Override
	public GengralUser findUserById(int id) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from gengral_user where id=?";
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			rs = psmt.executeQuery();
			GengralUser user = new GengralUser();
			while (rs.next()) {
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setSex(rs.getInt("sex"));
				user.setAge(rs.getString("age"));
				user.setNumber(rs.getString("number"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				user.setAdddate(rs.getString("add_date"));
				user.setDeletedate(rs.getString("delete_date"));
				if (rs.getString("change_user") == null) {
					user.setChangeuser(null);
				} else {
					user.setChangeuser(admin_username(Integer.parseInt(rs.getString("change_user"))));
				}
				user.setState(rs.getInt("state"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return null;
	}

	// 通过id来获取用户名在admin_user表中
	public String admin_username(int id) {
		Connection conn = ConnectionFactory.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String username = null;
		try {
			String sql = "select username from admin_user where id=?";
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			rs = psmt.executeQuery();
			while (rs.next()) {
				username = rs.getString("username");
				return username;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, psmt, rs);
		}
		return null;
	}
}
